﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace AJAX_learning
{
	[DataObject]
	public class EmployeeDb
	{
		#region Private fields

		private string _connectionString;

		#endregion


		#region Constructors

		public EmployeeDb() { this._connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString; }
		public EmployeeDb(string connectionString) { this._connectionString = connectionString; }

		#endregion


		#region Public methods

		[DataObjectMethod(DataObjectMethodType.Update, true)]
		public void UpdateEmployee(EmployeeDetails employee)
		{
			var con = new SqlConnection(_connectionString);
			var cmd = new SqlCommand("UpdateEmployee", con)
			{
				CommandType = CommandType.StoredProcedure
			};

			cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
			cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
			cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
			cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int));

			cmd.Parameters["@FirstName"].Value = employee.FirstName;
			cmd.Parameters["@LastName"].Value = employee.LastName;
			cmd.Parameters["@TitleOfCourtesy"].Value = employee.TitleOfCourtesy;
			cmd.Parameters["@EmployeeID"].Value = employee.EmployeeID;

			try
			{
				con.Open();
				cmd.ExecuteNonQuery();
			}
			catch (SqlException err)
			{
				throw new ApplicationException("Ошибка данных");
			}
			finally
			{
				con.Close();
			}

		}

		[DataObjectMethod(DataObjectMethodType.Insert, true)]
		public int InsertEmployee(EmployeeDetails employee)
		{
			var con = new SqlConnection(_connectionString);

			var cmd = new SqlCommand("InsertEmployee", con)
			{
				CommandType = CommandType.StoredProcedure
			};

			cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
			cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
			cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
			cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int));

			cmd.Parameters["@FirstName"].Value = employee.FirstName;
			cmd.Parameters["@LastName"].Value = employee.LastName;
			cmd.Parameters["@TitleOfCourtesy"].Value = employee.TitleOfCourtesy;
			cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;

			try
			{
				con.Open();
				cmd.ExecuteNonQuery();
				return (int)cmd.Parameters["@EmployeeID"].Value;
			}
			catch (SqlException err)
			{
				throw new ApplicationException("Ошибка данных");
			}
			finally
			{
				con.Close();
			}
		}

		[DataObjectMethod(DataObjectMethodType.Select)]
		public EmployeeDetails GetEmployee(int employeeId)
		{
			var con = new SqlConnection(_connectionString);
			var cmd = new SqlCommand("GetEmployee", con)
			{
				CommandType = CommandType.StoredProcedure
			};

			cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int));
			cmd.Parameters["@EmployeeID"].Value = employeeId;

			try
			{
				con.Open();
				var reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
				if (!reader.HasRows) return null;
				reader.Read();
				var employeeDetails = new EmployeeDetails((int)reader["EmployeeID"], (string)reader["FirstName"],
														  (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
				reader.Close();
				return employeeDetails;
			}
			catch (SqlException err)
			{
				throw new ApplicationException("Ошибка данных");
			}
			finally
			{
				con.Close();
			}
		}

		[DataObjectMethod(DataObjectMethodType.Select, true)]
		public List<EmployeeDetails> GetEmployees()
		{
			var con = new SqlConnection(_connectionString);
			var cmd = new SqlCommand("GetAllEmployees", con)
			{
				CommandType = CommandType.StoredProcedure
			};
			var employeesList = new List<EmployeeDetails>();
			try
			{
				con.Open();
				var reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					string firstName = reader["FirstName"] == DBNull.Value ? String.Empty : (string) reader["FirstName"];
					string lastName = reader["LastName"] == DBNull.Value ? String.Empty : (string) reader["LastName"];
					string titleOfCourtesy = reader["TitleOfCourtesy"] == DBNull.Value ? String.Empty : (string) reader["TitleOfCourtesy"];
					employeesList.Add(new EmployeeDetails((int)reader["EmployeeID"], firstName, lastName, titleOfCourtesy));
				}
				reader.Close();
				return employeesList;
			}
			catch (SqlException err)
			{
				throw new ApplicationException("Ошибка данных");
			}
			finally
			{
				con.Close();
			}
		}

		[DataObjectMethod(DataObjectMethodType.Select)]
		public int CountEmployees()
		{
			var con = new SqlConnection(_connectionString);
			var cmd = new SqlCommand("CountEmployees", con) { CommandType = CommandType.StoredProcedure };

			try
			{
				con.Open();
				return (int)cmd.ExecuteScalar();
			}
			catch (SqlException err)
			{
				throw new ApplicationException("Ошибка данных");
			}
			finally
			{
				con.Close();
			}
		}

		[DataObjectMethod(DataObjectMethodType.Delete,true)]
		public void DeleteEmployee(int employeeId)
		{
			var con = new SqlConnection(_connectionString);
			var cmd = new SqlCommand("DeleteEmployee", con)
			{
				CommandType = CommandType.StoredProcedure
			};

			cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int));
			cmd.Parameters["@EmployeeID"].Value = employeeId;

			try
			{
				con.Open();
				cmd.ExecuteNonQuery();
			}
			catch (SqlException err)
			{
				throw new ApplicationException("Ошибка данных");
			}
			finally
			{
				con.Close();
			}
		}

		#endregion
	}
}